package edu.bw.dao.impl;

import edu.bw.dao.RoleDao;
import edu.bw.dto.SelectRoleByConditionForm;
import edu.bw.dto.UpdateRoleForm;
import edu.bw.pojo.Role;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class RoleDaoImpl implements RoleDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    @Override
    public List<Map<String, Object>> selectDeptByCondition(SelectRoleByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder(" SELECT r.id,r.role_name AS roleName,COUNT(u.id) AS users,JSON_LENGTH(r.permissions) AS permissions,r.`desc`,r.systemic " +
                "FROM bw_zzy_role r LEFT JOIN bw_zzy_users u " +
                "ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) WHERE 1 = 1 ");
        //queryParams用来设置?参数值
        List<Object> queryParams = new ArrayList<>();
        if (selectCondition.getRoleName() != null && !selectCondition.getRoleName().isEmpty()) {
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%" + selectCondition.getRoleName() + "%");
        }

        sql.append(" GROUP BY r.id LIMIT ?, ? ");
        queryParams.add((selectCondition.getPage() - 1) * selectCondition.getLength());
        queryParams.add(selectCondition.getLength());

        try {
            return qr.query(sql.toString(), new MapListHandler(), queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Long SelectDeptByConditionCount(SelectRoleByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM" +
                "(SELECT r.id FROM bw_zzy_role r " +
                "LEFT JOIN bw_zzy_users u " +
                "ON JSON_CONTAINS(u.role, CONVERT(r.id,CHAR)) " +
                "WHERE 1 = 1  ");
        //queryParams用来设置?参数值
        List<Object> queryParams = new ArrayList<>();
        if (selectCondition.getRoleName() != null && !selectCondition.getRoleName().isEmpty()) {
            sql.append(" AND r.role_name LIKE ? ");
            queryParams.add("%" + selectCondition.getRoleName() + "%");
        }

        sql.append("GROUP BY r.id) AS temp");

        try {
            return qr.query(sql.toString(), new ScalarHandler<>(), queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * INSERT INTO bw_zzy_role SET role_name = '测试01' , permissions = '[1,2,3]' , `desc` = '测试01';
     * @param role
     * @return
     */
    @Override
    public Integer insert(Role role) {
        StringBuilder sql = new StringBuilder("INSERT INTO bw_zzy_role SET role_name = ? , permissions = ? ");

        List<Object> insertParams = new ArrayList<>();
        insertParams.add(role.getRoleName());
        insertParams.add(role.getPermissions());

        // desc在页面为非必填项,当有值的时候我们再插入其值,无值不插入
        if (role.getDesc()!=null && !role.getDesc().isEmpty()) {
            sql.append(" , `desc`= ? ");
            insertParams.add(role.getDesc());
        }
        try {
            return qr.update(sql.toString(), insertParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query(" SELECT id,role_name AS roleName,permissions,`desc`,default_permissions AS defaultPermissions " +
                    " FROM bw_zzy_role WHERE id = ? ", new MapListHandler(), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(UpdateRoleForm updateRoleForm) {
        try {
            return qr.update("UPDATE bw_zzy_role SET role_name = ?,`desc` = ?,permissions = ? WHERE id = ? ",
                    updateRoleForm.getRoleName(),
                    updateRoleForm.getDesc(),
                    updateRoleForm.getPermissions(),
                    updateRoleForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT IF(SUM(temp.users) > 0,false,true)AS result FROM " +
                " ( SELECT COUNT( u.id ) AS users FROM bw_zzy_role r INNER JOIN bw_zzy_users u ON JSON_CONTAINS " +
                " ( u.role, CONVERT ( r.id, JSON ) ) WHERE u.id IN ( ");
        
        if (ids != null && !ids.isEmpty()) {
            sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")));
        }
        
        sql.append(") \n" +
                "    GROUP BY\n" +
                "      r.id\n" +
                "  ) AS temp;");
        try {
            Long result = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());
            return result == 0 ? false : true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer deleteRoleByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_role WHERE id IN ( ");
        if (ids != null && !ids.isEmpty()) {
            sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", ")));
        }
        sql.append(") AND systemic = FALSE; ");
        try {
            return qr.update(sql.toString(), ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectAllRole() {
        try {
            return qr.query("SELECT id,role_name AS roleName " +
                    "  FROM bw_zzy_role  " +
                    "  ORDER BY id ", new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
